<?php
//导出示例：
//1. require './extend/base/files/PHPExeclCore.php';
//2. \PHPExeclCore::RecursionCreateExecl($xlsName,$Head,$data,$isSaveLocal);
// $Head = array(
//      array('value'=>'姓名','col'=>2,'row'=>2,'width'=>20,'type'=>'list','allowarray'=>'邓蔚之,李'),
//      array('value'=>'第一天','col'=>2,'row'=>1,'width'=>20,'Content'=>'2014-12-29号',
//          'children'=>array(
//              array('value'=>'中午','col'=>1,'width'=>20,'type'=>'range','allowarray'=>'10,100'),
//              array('value'=>'晚上','width'=>20),
//          )
//      )
//	);
// $data = array(
//          array('邓蔚之','12','吃饭1','睡觉1','起床刷牙2','吃饭睡觉2'),
//          array('邓蔚之','10','吃饭1','睡觉1','起床刷牙2','吃饭睡觉2'),
//      );
//
// 获取示例：
// 1. require root_path().'/extend/base/files/PHPExeclCore.php';
// 2. \PHPExeclCore::getExcelData($file);
//
require_once root_path().'extend/lib/phpExcel/PHPExcel/IOFactory.php';
header("Content-Type: text/html; charset=utf-8");
header('Access-Control-Allow-Method:POST,GET');

class PHPExeclCore extends PHPExcel_IOFactory{
    public static function SummerCreateExecl($Head,$data){       
        self::SummerCreateExeclHead($Head,$data,"Excel2007");
    }
     
    public static function SummerReadExecl($dir){
        if(!file_exists($dir)){
            echo "Execl Not Exist";
        }else{
            $PHPExeclObj =  self::load($dir);
            $sheetCount = $PHPExeclObj->getSheetCount(); //得到Execl中包含的Sheet工作簿的数量
            for($i=0;$i<$sheetCount;$i++){
                $ActiveSheet = $PHPExeclObj->getSheet($i);
                $highestRow = $ActiveSheet->getHighestRow(); // 取得总列数
                $allColumn  = $ActiveSheet->getHighestColumn();
 
                //通过嵌套循环来读取sheet工作簿里面的内容
                for($Col='A';$Col<$allColumn;$Col++){
                    for($Row=1;$Row<$highestRow;$Row++){
                        $Data[$Col][$Row] = $ActiveSheet->getCell($Col.$Row)->getValue();
                    }
                }
 
            }
        }
        return $Data;
    }
     
     
    /*
     * 将数据写入到数据表中
     * $Data Array 表示要插入进Execl数据
     * $RuleData Array 表示数据格式的规则数组
     * $i int 表示从第几行起的插入数据
     * **/
     
    public static function SummerInsertDateToExecl($sheet,$Head,$Data,$n=3,$RuleData=array())
    {
        $SimpleHead = self::getHead($Head);
		
        $row = $n;
        foreach($Data as $key=>$valueArr)
        {       
            $m = 0;
            foreach($valueArr as $k=>$v)
            {
            	
                $StartCol = PHPExcel_Cell::stringFromColumnIndex($m).$row;
                //$sheet->getCell($StartCol)->setValue($v);
				//设置为文本格式
                $sheet->setCellValueExplicit($StartCol,$v,PHPExcel_Cell_DataType::TYPE_STRING); 
				             
                if(isset($SimpleHead[$k]['col']))
                {
                    $m = $m + $SimpleHead[$k]['col']-1;
                    $endCol = PHPExcel_Cell::stringFromColumnIndex($m).$row;
                    $sheet->mergeCells($StartCol.":".$endCol);
                }
                 
                $m++;
                 
                $type = false;
                 
                if(isset($SimpleHead[$k]['type']))
                {
                    $type = $SimpleHead[$k]['type'];
                    $AllowArray = $SimpleHead[$k]['allowarray'];
                }
                 
                //设置单元格的数据验证
                if($type)
                {
                    switch ($type)
                    {
                        case 'list':
                            self::setSelectionRange($sheet, $StartCol,$AllowArray);
                        break;
                        case 'range':
                            self::setValueRange($sheet, $StartCol,$AllowArray);
                        break;
                    }
                }
 
            }
            $row ++ ;
        }
        
    }
         
    /*
     * 生成Execl单元格备注
     * $sheet 当前的工作簿对象
     * $Cell 需要设置属性的单元格
     * $content 备注内容
     * */
    private static function setComment($sheet,$Cell,$content){
        $sheet->getComment($Cell)->setAuthor('备注');
        $objCommentRichText = $sheet->getComment($Cell)->getText()->createTextRun('备注:');
        $objCommentRichText->getFont()->setBold(true);
        $sheet->getComment($Cell)->getText()->createTextRun("\r\n");
        $sheet->getComment($Cell)->getText()->createTextRun($content);
        $sheet->getComment($Cell)->setWidth('100pt');
        $sheet->getComment($Cell)->setHeight('100pt');
        $sheet->getComment($Cell)->setMarginLeft('150pt');
        $sheet->getComment($Cell)->getFillColor()->setRGB('EEEEEE');
    }
     
    /*
     * 现在单元格的有效数据范围，暂时仅限于数字
     * $sheet 当前的工作簿对象
     * $Cell 需要设置属性的单元格
     * $ValueRange array 允许输入数组的访问
     */
    private static function setValueRange($sheet,$Cell,$ValueRange){
        //设置单元格的的数据类型是数字，并且保留有效位数
        $sheet->getStyle($Cell)->getNumberFormat()
        ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_00);
         
        $ValueRange = explode(",",$ValueRange);
         
        //开始数值有效访问设定
        $objValidation = $sheet->getCell($Cell)->getDataValidation();
        $objValidation->setType( PHPExcel_Cell_DataValidation:: TYPE_WHOLE );
        $objValidation->setErrorStyle( PHPExcel_Cell_DataValidation:: STYLE_STOP );
        $objValidation->setAllowBlank(true);
        $objValidation->setShowInputMessage( true);            //设置显示提示信息
        $objValidation->setShowErrorMessage( true);            //设置显示错误信息
        $objValidation->setErrorTitle('输入错误');              //错误标题
        $objValidation->setError('请输入数据范围在从'.$ValueRange[0].'到'.$ValueRange[1].'之间的所有值');       //错误内容
        $objValidation->setPromptTitle('允许输入');         //设置提示标题
        $objValidation->setPrompt('请输入数据范围在从'.$ValueRange[0].'到'.$ValueRange[1].'之间的所有值'); //提示内容
        $objValidation->setFormula1($ValueRange['0']);     //设置最大值
        $objValidation->setFormula2($ValueRange['1']);     //设置最小值
    }
     
    private static function OutinputHeader($objWriter,$xlsName,$isSaveLocal=false){
    	$fileName = iconv('utf-8','gb2312', $xlsName);//文件名称
    	$fileName = $xlsName;

    	if(!$isSaveLocal){
    		header("Content-Type: application/force-download");
	        header("Content-Type: application/octet-stream");
	        header("Content-Type: application/download");
			header("Content-Disposition:attachment;filename=$fileName.xls");
	        header("Content-Transfer-Encoding: binary");
	        header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT");
	        header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
	        header("Pragma: no-cache");
			$objWriter->save('php://output');
			exit;
    	}else{
    		$objWriter->save("./backup/$fileName.xls");
    	}
    }
     
    //数据控制，设置单元格数据在一个可选方位类
    private static function setSelectionRange($sheet,$Cell,$rangeStr,$Title="数据类型"){
        $objValidation = $sheet->getCell($Cell)->getDataValidation();
        $objValidation -> setType(PHPExcel_Cell_DataValidation::TYPE_LIST)
            -> setErrorStyle(PHPExcel_Cell_DataValidation::STYLE_STOP)
            -> setAllowBlank(true)
            -> setShowInputMessage(true)
            -> setShowErrorMessage(true)
            -> setShowDropDown(true)
            -> setErrorTitle('输入的值有误')
            -> setError('您输入的值不在下拉框列表内.')
            -> setPromptTitle('"'.$Title.'"')
            -> setFormula1('"'.$rangeStr.'"');   
    }
     
    /*
     * 构建表头
     * */
    public static function RecursionCreateExecl($xlsName,$head,$data,$isSaveLocal=false){
 
        $PHPExecl = new PHPExcel();
             
        $objWriter = self::createWriter($PHPExecl, 'Excel2007');
         
        $PHPExecl->getProperties()->setCreator("4399om")
        ->setLastModifiedBy("Summer")
        ->setTitle("Office 2007 XLSX Test Document")
        ->setSubject("Office 2007 XLSX Test Document")
        ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
        ->setKeywords("office 2007 openxml php")
        ->setCategory("Test result file");
         
        $PHPExecl->setActiveSheetIndex(0);
         
        $sheet = $PHPExecl->getActiveSheet();
         
        self::HandleHeadToNode($sheet, $head,1,0,0);
        
        $rowIndex = [2];
        foreach($head as $val){
        	if($val["children"]){
        		array_push($rowIndex,3);
        		foreach($val["children"] as $children){
        			if($children["children"]){
        				array_push($rowIndex,4);
        			}
        		}
        	}
        }

        self::SummerInsertDateToExecl($sheet,$head,$data,max($rowIndex));
 		
        self::OutinputHeader($objWriter,$xlsName,$isSaveLocal);
 	}
  
 	private static function HandleHeadToNode($sheet,$Head,$beginRow,$col,$StartCol){
    	foreach($Head as $key=>$cells){
            $row = $beginRow; //表示行
         
            $beginCol = PHPExcel_Cell::stringFromColumnIndex($col).$row;
             
            $sheet->getCell($beginCol)->setValue($cells['value']);
             
            //设置表格样式
            $sheet->getStyle($beginCol)->getAlignment()->applyFromArray(
                array(
                    'horizontal'=> PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
                    'vertical'  => PHPExcel_Style_Alignment::VERTICAL_CENTER,
                    'rotation'  => 0,
                    'wrap'      => TRUE,
                )
            );
            //字体
            $sheet->getStyle($beginCol)->getFont()->getColor() ->setARGB("00F92657");
            $sheet->getStyle($beginCol)->getFont()->setBold(true);//粗体
            //背景
            $sheet->getStyle($beginCol)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)
            ->getStartColor()->setARGB("00ececec");
      		//边框
      		$style_array = array(
                'borders' => array(
                    'allborders' => array(
                        'style' => \PHPExcel_Style_Border::BORDER_THIN,
						'color' => array('argb' => "00D4D4D4"),  
                    )
                )                                                                                                                                                       			);
            $sheet->getStyle($beginCol)->applyFromArray($style_array);
      		
            //设置单元格的宽度
            if(isset($cells['width'])){
                $Cell = $sheet->getColumnDimension(PHPExcel_Cell::stringFromColumnIndex($col));
                $Cell->setWidth($cells['width']);
            }
             
            //哥元素打上标记
            if(isset($cells['Content'])){
                self::setComment($sheet, $beginCol, $cells['Content']);
            }
              
            $merge = false; //合并单元格
            if(isset($cells['col'])) {
                $col += $cells['col']-1;
                $merge = true;
            }
              
            if(isset($cells['row'])){
                $row += $cells['row']-1;
                $merge = true;
            }
             
            if($merge){
                $endCol = PHPExcel_Cell::stringFromColumnIndex($col).$row;
	            $sheet->getStyle($endCol)->applyFromArray($style_array);
	           	$endCol2 = PHPExcel_Cell::stringFromColumnIndex($col).($row-1);
	            $sheet->getStyle($endCol2)->applyFromArray($style_array);
                $sheet->mergeCells($beginCol.":".$endCol);
            }
             
            $row ++;    
            $col ++;
         
            //表示有存在孩子节点
            if(isset($cells['children']) && is_array($cells['children'])){ 
                $cols = $StartCol;
                $n = count($cells['children']);
                if(!self::IsExistChildren($cells['children'])){       
                    $cols     = $col-$n;
                    $StartCol = $col;
                }
                self::HandleHeadToNode($sheet,$cells['children'],$row,$cols,$StartCol);
            }else{
                $StartCol = $col;
            }
 
     	}
 	}
 
   //判断自己的孩子节点中是否存在孙子节点
  	private static function IsExistChildren($Data){
      	foreach($Data as $key=>$value){
           if(isset($value['children']) && is_array($value['children'])){
               return true;
           }
      	}
      	return false;
  	}
   
  	//获取底层数据
  	private static function getHead($Head,&$Node=array()){
	    foreach($Head as $key=>$value){
	        if(isset($value['children']) && is_array($value['children'])){
	            self::getHead($value['children'],$Node);
	        } else {   
	            $Node[] = $value;
	        }
	    }
	    return $Node;
  	}
   
   
   /**
	 * 导入excel文件
	 * @param  string $file  excel文件路径
	 * @return array        	  excel文件内容数组
	 */
	public function getExcelData($file){
		if(!file_exists($file)){ 
			return [];
		};	
	    $type = pathinfo($file); 
	    $type = strtolower($type["extension"]);
		
	    // 判断使用哪种格式
	    if($type=='xls'){
			$type= 'Excel2007';
			require_once root_path().'/extend/lib/phpExcel/PHPExcel/Reader/Excel2007.php';
		}else if($type=='xlsx'){
			$type= 'Excel2007';
			require_once root_path().'/vendor/lib/phpExcel/PHPExcel/Reader/Excel2007.php';
		}

	    $objReader = \PHPExcel_IOFactory::createReader($type);
	    $objPHPExcel = $objReader->load($file); 
	    $sheet = $objPHPExcel->getSheet(0); 
	    // 取得总行数 
	    $highestRow = $sheet->getHighestRow();     
	    // 取得总列数      
	    $highestColumn = $sheet->getHighestColumn(); 
		
	
		
	    //循环读取excel文件,读取一条,插入一条
	    $data=array();
	    //从第一行开始读取数据
	    for($j=1;$j<=$highestRow;$j++){
	        //从A列读取数据
	        for($k='A';$k<=$highestColumn;$k++){
	            // 读取单元格
	            $data[$j][]=$objPHPExcel->getActiveSheet()->getCell("$k$j")->getValue();
	        } 
	    }  
	    return $data;
	}
   
}